const express = require('express');
const { promisePool } = require('../config/database');
const { mlaApplicationSchema, mlaReviewSchema } = require('../schema/minLivingAllowance');
const { validateApplicationConsistency, validateApplicationsList } = require('../utils/dataValidator');

const router = express.Router();

// 生成申请编号
function generateApplicationNo() {
  const now = new Date();
  const year = now.getFullYear();
  const month = String(now.getMonth() + 1).padStart(2, '0');
  const day = String(now.getDate()).padStart(2, '0');
  const timestamp = now.getTime().toString().slice(-6);
  return `MLA${year}${month}${day}${timestamp}`;
}

// 提交最低生活保障申请
router.post('/apply', async (req, res) => {
  try {
    // 验证请求数据
    const { error, value } = mlaApplicationSchema.validate(req.body);
    if (error) {
      return res.status(400).json({
        success: false,
        message: error.details[0].message
      });
    }

    const {
      userId,
      applicantName,
      applicantIdCard,
      applicantPhone,
      applicantAddress,
      familySize,
      monthlyIncome,
      incomeSource,
      difficultyReason,
      idCardImage,
      householdImage,
      incomeProofImage
    } = value;

    // 检查用户是否存在
    const [users] = await promisePool.execute(
      'SELECT id, name FROM users WHERE id = ? AND deleted_at IS NULL',
      [userId]
    );

    if (users.length === 0) {
      return res.status(404).json({
        success: false,
        message: '用户不存在'
      });
    }

    // 检查是否已有待审核的申请
    const [existingApplications] = await promisePool.execute(
      'SELECT id FROM min_living_allowance_applications WHERE user_id = ? AND status = "pending" AND deleted_at IS NULL',
      [userId]
    );

    if (existingApplications.length > 0) {
      return res.status(400).json({
        success: false,
        message: '您已有待审核的申请，请勿重复提交'
      });
    }

    // 生成申请编号
    const applicationNo = generateApplicationNo();

    // 开始事务
    const connection = await promisePool.getConnection();
    await connection.beginTransaction();

    try {
      // 插入申请记录
      const [applicationResult] = await connection.execute(
        `INSERT INTO min_living_allowance_applications (
          application_no, user_id, applicant_name, applicant_id_card, 
          applicant_phone, applicant_address, family_size, monthly_income, 
          income_source, difficulty_reason, id_card_image, household_image, 
          income_proof_image, apply_time, created_at, updated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW(), NOW())`,
        [
          applicationNo, userId, applicantName, applicantIdCard,
          applicantPhone, applicantAddress, familySize, monthlyIncome,
          incomeSource, difficultyReason, idCardImage, householdImage,
          incomeProofImage
        ]
      );

      const applicationId = applicationResult.insertId;

      // 插入申请进度记录
      await connection.execute(
        `INSERT INTO min_living_allowance_progress (
          application_id, step_name, step_status, step_order, 
          operator_name, operation_time, operation_comment, next_step, 
          created_at, updated_at
        ) VALUES (?, ?, ?, ?, ?, NOW(), ?, ?, NOW(), NOW())`,
        [
          applicationId, '申请提交', 'completed', 1,
          applicantName, '申请人提交申请材料', '材料初审'
        ]
      );

      // 插入处理记录
      await connection.execute(
        `INSERT INTO min_living_allowance_process_logs (
          application_id, action_type, action_description, operator_id, 
          operator_name, operator_role, action_comment, action_time, 
          created_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), NOW())`,
        [
          applicationId, 'submit', '提交申请', userId,
          applicantName, 'citizen', '申请人提交最低生活保障申请'
        ]
      );

      // 如果有附件，插入附件记录
      if (idCardImage) {
        await connection.execute(
          `INSERT INTO min_living_allowance_attachments (
            application_id, attachment_type, file_name, file_url, 
            upload_time, created_at, updated_at
          ) VALUES (?, ?, ?, ?, NOW(), NOW(), NOW())`,
          [applicationId, 'id_card', '身份证照片', idCardImage]
        );
      }

      if (householdImage) {
        await connection.execute(
          `INSERT INTO min_living_allowance_attachments (
            application_id, attachment_type, file_name, file_url, 
            upload_time, created_at, updated_at
          ) VALUES (?, ?, ?, ?, NOW(), NOW(), NOW())`,
          [applicationId, 'household', '户口簿照片', householdImage]
        );
      }

      if (incomeProofImage) {
        await connection.execute(
          `INSERT INTO min_living_allowance_attachments (
            application_id, attachment_type, file_name, file_url, 
            upload_time, created_at, updated_at
          ) VALUES (?, ?, ?, ?, NOW(), NOW(), NOW())`,
          [applicationId, 'income_proof', '收入证明', incomeProofImage]
        );
      }

      // 提交事务
      await connection.commit();

      res.json({
        success: true,
        message: '申请提交成功',
        data: {
          applicationId: applicationId,
          applicationNo: applicationNo,
          status: 'pending',
          applyTime: new Date().toISOString()
        }
      });

    } catch (error) {
      // 回滚事务
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }

  } catch (error) {
    console.error('提交申请接口错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});

// 获取用户的最低生活保障申请列表
router.get('/my-applications/:userId', async (req, res) => {
  try {
    const { userId } = req.params;
    const { page = 1, limit = 10, status } = req.query;

    console.log('收到请求:', { userId, page, limit, status });

    // 验证用户ID
    if (!userId || isNaN(parseInt(userId))) {
      return res.status(400).json({
        success: false,
        message: '用户ID格式不正确'
      });
    }

    // 验证分页参数
    const pageNum = parseInt(page);
    const limitNum = parseInt(limit);
    const offset = (pageNum - 1) * limitNum;

    if (pageNum < 1 || limitNum < 1 || limitNum > 100) {
      return res.status(400).json({
        success: false,
        message: '分页参数不正确'
      });
    }

    console.log('开始查询数据库...');

    // 先测试基本查询
    try {
      const [testResult] = await promisePool.execute('SELECT 1 as test');
      console.log('数据库连接正常:', testResult);
    } catch (testError) {
      console.error('数据库连接测试失败:', testError);
      console.error('连接错误详情:', {
        message: testError.message,
        code: testError.code,
        errno: testError.errno,
        sqlState: testError.sqlState
      });
      return res.status(500).json({
        success: false,
        message: '数据库连接失败: ' + testError.message,
        error: {
          code: testError.code,
          errno: testError.errno
        }
      });
    }

    // 检查表是否存在
    let tableExists = false;
    try {
      const [tableCheck] = await promisePool.execute(`
        SELECT COUNT(*) as count 
        FROM information_schema.TABLES 
        WHERE TABLE_SCHEMA = DATABASE() 
        AND TABLE_NAME = 'min_living_allowance_applications'
      `);
      tableExists = tableCheck[0].count > 0;
      console.log('表存在检查结果:', tableExists);
    } catch (tableError) {
      console.error('检查表存在性失败:', tableError);
      tableExists = false;
    }
    
    if (!tableExists) {
      console.log('数据表不存在，先创建表');
      
      // 创建表（简化版本，不包含外键约束）
      const createTableSQL = `
        CREATE TABLE IF NOT EXISTS \`min_living_allowance_applications\` (
          \`id\` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '申请ID',
          \`application_no\` varchar(50) NOT NULL COMMENT '申请编号（系统生成）',
          \`user_id\` bigint(20) NOT NULL COMMENT '申请人ID，关联users表',
          \`status\` enum('pending','approved','rejected','completed') NOT NULL DEFAULT 'pending' COMMENT '申请状态',
          \`applicant_name\` varchar(100) NOT NULL COMMENT '申请人姓名',
          \`applicant_id_card\` varchar(18) NOT NULL COMMENT '申请人身份证号',
          \`applicant_phone\` varchar(20) NOT NULL COMMENT '申请人联系电话',
          \`applicant_address\` varchar(200) NOT NULL COMMENT '申请人详细地址',
          \`family_size\` int(11) NOT NULL COMMENT '家庭人口数',
          \`monthly_income\` decimal(10,2) NOT NULL COMMENT '家庭月收入（元）',
          \`income_source\` varchar(100) NOT NULL COMMENT '主要收入来源',
          \`difficulty_reason\` text NOT NULL COMMENT '困难原因说明',
          \`id_card_image\` varchar(500) DEFAULT NULL COMMENT '身份证照片URL',
          \`household_image\` varchar(500) DEFAULT NULL COMMENT '户口簿照片URL',
          \`income_proof_image\` varchar(500) DEFAULT NULL COMMENT '收入证明照片URL（可选）',
          \`reviewer_id\` bigint(20) DEFAULT NULL COMMENT '审核人ID，关联users表',
          \`reviewer_name\` varchar(100) DEFAULT NULL COMMENT '审核人姓名',
          \`review_comment\` text DEFAULT NULL COMMENT '审核意见',
          \`review_time\` datetime DEFAULT NULL COMMENT '审核时间',
          \`processor_id\` bigint(20) DEFAULT NULL COMMENT '处理人ID，关联users表',
          \`processor_name\` varchar(100) DEFAULT NULL COMMENT '处理人姓名',
          \`process_comment\` text DEFAULT NULL COMMENT '处理意见',
          \`process_time\` datetime DEFAULT NULL COMMENT '处理时间',
          \`apply_time\` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '申请时间',
          \`deadline\` datetime DEFAULT NULL COMMENT '处理截止时间',
          \`created_at\` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
          \`updated_at\` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
          \`deleted_at\` timestamp NULL DEFAULT NULL COMMENT '删除时间（软删除）',
          PRIMARY KEY (\`id\`),
          UNIQUE KEY \`uk_application_no\` (\`application_no\`),
          KEY \`idx_user_id\` (\`user_id\`),
          KEY \`idx_status\` (\`status\`),
          KEY \`idx_apply_time\` (\`apply_time\`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='最低生活保障申请表'
      `;
      
      try {
        await promisePool.execute(createTableSQL);
        console.log('✅ 数据表创建成功');
        
        // 插入一些测试数据
        const insertTestData = `
          INSERT INTO min_living_allowance_applications (
            application_no, user_id, applicant_name, applicant_id_card, 
            applicant_phone, applicant_address, family_size, monthly_income, 
            income_source, difficulty_reason, apply_time
          ) VALUES 
          ('MLA202401150001', ?, '张三', '410721199001011234', '13900139000', 
           '河南省新乡县七里营村123号', 3, 1200.00, '务农收入', 
           '家庭主要劳动力因病丧失劳动能力，收入微薄，生活困难', '2024-01-15 10:30:00'),
          ('MLA202401200001', ?, '李四', '410721198502151234', '13700137000', 
           '河南省新乡县七里营村456号', 4, 1500.00, '打工收入', 
           '夫妻双方均为临时工，收入不稳定，子女上学费用高', '2024-01-20 09:15:00')
          ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP
        `;
        
        await promisePool.execute(insertTestData, [userId, userId]);
        console.log('✅ 测试数据插入成功');
        
      } catch (createError) {
        console.error('创建表或插入数据失败:', createError);
        // 继续执行，返回空数据
      }
    }

    console.log('开始查询真实数据');

    // 检查用户是否存在（简化处理，如果users表不存在就跳过检查）
    let userExists = true;
    try {
      const [users] = await promisePool.execute(
        'SELECT id, name FROM users WHERE id = ?',
        [userId]
      );
      userExists = users.length > 0;
      console.log('用户存在检查结果:', userExists);
    } catch (userError) {
      console.log('用户表不存在或查询失败，跳过用户检查:', userError.message);
      userExists = true; // 假设用户存在
    }

    if (!userExists) {
      return res.status(404).json({
        success: false,
        message: '用户不存在'
      });
    }

    // 构建查询条件
    let whereClause = 'WHERE user_id = ?';
    let queryParams = [userId];

    // 添加状态筛选
    if (status && status.trim() !== '' && ['pending', 'approved', 'rejected', 'completed'].includes(status)) {
      whereClause += ' AND status = ?';
      queryParams.push(status);
    }

    console.log('查询条件:', { whereClause, queryParams });

    // 查询总数
    const [countResult] = await promisePool.execute(
      `SELECT COUNT(*) as total FROM min_living_allowance_applications ${whereClause}`,
      queryParams
    );
    const total = countResult[0].total;

    console.log('总数查询完成:', total);

    // 查询申请列表
    const [applications] = await promisePool.execute(
      `SELECT 
        id, application_no, status, applicant_name, applicant_id_card, 
        applicant_phone, applicant_address, family_size, monthly_income, 
        income_source, difficulty_reason, id_card_image, household_image, 
        income_proof_image, reviewer_name, review_comment, review_time, 
        processor_name, process_comment, process_time, apply_time, 
        deadline, created_at, updated_at
      FROM min_living_allowance_applications 
      ${whereClause}
      ORDER BY apply_time DESC 
      LIMIT ${limitNum} OFFSET ${offset}`,
      queryParams
    );

    // 为每个申请添加标准字段并格式化时间
    const formattedApplications = applications.map(app => ({
      ...app,
      title: '最低生活保障申请',
      category: '民政社保',
      dept: '民政办',
      description: '困难群众最低生活保障资格申请与复核',
      // 确保时间字段格式正确
      apply_time: app.apply_time ? new Date(app.apply_time).toISOString().slice(0, 19).replace('T', ' ') : null,
      review_time: app.review_time ? new Date(app.review_time).toISOString().slice(0, 19).replace('T', ' ') : null,
      process_time: app.process_time ? new Date(app.process_time).toISOString().slice(0, 19).replace('T', ' ') : null
    }));

    console.log('申请列表查询完成，结果数量:', applications.length);
    console.log('原始申请数据（前3条）:', JSON.stringify(applications.slice(0, 3), null, 2));
    console.log('格式化后的申请数据（前3条）:', JSON.stringify(formattedApplications.slice(0, 3), null, 2));

    // 计算分页信息
    const totalPages = Math.ceil(total / limitNum);
    const hasNextPage = pageNum < totalPages;
    const hasPrevPage = pageNum > 1;

    const responseData = {
      success: true,
      message: '获取申请列表成功（真实数据）',
      data: {
        applications: formattedApplications,
        pagination: {
          page: pageNum,
          limit: limitNum,
          total: total,
          pages: totalPages,
          hasNextPage: hasNextPage,
          hasPrevPage: hasPrevPage
        }
      }
    };

    console.log('=== 准备返回的响应数据 ===');
    console.log('applications数量:', formattedApplications.length);
    console.log('第一条申请的时间:', formattedApplications[0]?.apply_time);

    res.json(responseData);

  } catch (error) {
    console.error('获取申请列表接口错误:', error);
    console.error('错误详情:', {
      message: error.message,
      code: error.code,
      errno: error.errno,
      sqlState: error.sqlState,
      sqlMessage: error.sqlMessage,
      stack: error.stack
    });
    
    // 根据错误类型返回不同的错误信息
    let errorMessage = '服务器内部错误';
    if (error.code === 'ER_NO_SUCH_TABLE') {
      errorMessage = '数据表不存在，请检查数据库配置';
    } else if (error.code === 'ER_ACCESS_DENIED_ERROR') {
      errorMessage = '数据库访问被拒绝，请检查数据库连接配置';
    } else if (error.code === 'ECONNREFUSED') {
      errorMessage = '无法连接到数据库服务器';
    } else if (error.code === 'ER_BAD_DB_ERROR') {
      errorMessage = '数据库不存在，请先创建数据库';
    }
    
    res.status(500).json({
      success: false,
      message: errorMessage,
      error: process.env.NODE_ENV === 'development' ? {
        message: error.message,
        code: error.code,
        stack: error.stack
      } : undefined
    });
  }
});

// 获取所有村民的最低生活保障申请列表（管理员用）
router.get('/admin/applications', async (req, res) => {
  try {
    const { page = 1, limit = 10, status, keyword, startDate, endDate } = req.query;

    console.log('管理员获取申请列表请求:', { page, limit, status, keyword, startDate, endDate });

    // 验证管理员权限
    const userId = req.headers['user-id'] || req.query.userId;
    const userRole = req.headers['user-role'] || req.query.userRole;
    
    if (!userId) {
      return res.status(401).json({
        success: false,
        message: '用户未登录'
      });
    }

    if (!userRole || userRole !== 'admin') {
      return res.status(403).json({
        success: false,
        message: '权限不足，仅管理员可访问'
      });
    }

    // 验证管理员用户是否存在
    try {
      const [users] = await promisePool.execute(
        'SELECT id, name, role FROM users WHERE id = ? AND role = "admin" AND deleted_at IS NULL',
        [userId]
      );

      if (users.length === 0) {
        return res.status(403).json({
          success: false,
          message: '管理员身份验证失败'
        });
      }

      console.log('管理员身份验证通过:', users[0]);
    } catch (userError) {
      console.error('验证管理员身份失败:', userError);
      return res.status(500).json({
        success: false,
        message: '身份验证失败'
      });
    }

    // 验证分页参数
    const pageNum = parseInt(page);
    const limitNum = parseInt(limit);
    const offset = (pageNum - 1) * limitNum;

    if (pageNum < 1 || limitNum < 1 || limitNum > 100) {
      return res.status(400).json({
        success: false,
        message: '分页参数不正确'
      });
    }

    // 构建查询条件
    let whereClause = 'WHERE deleted_at IS NULL';
    let queryParams = [];

    // 添加状态筛选
    if (status && status.trim() !== '' && ['pending', 'approved', 'rejected', 'completed'].includes(status)) {
      whereClause += ' AND status = ?';
      queryParams.push(status);
    }

    // 添加关键词搜索
    if (keyword) {
      whereClause += ' AND (applicant_name LIKE ? OR applicant_phone LIKE ? OR application_no LIKE ?)';
      const searchKeyword = `%${keyword}%`;
      queryParams.push(searchKeyword, searchKeyword, searchKeyword);
    }

    // 添加时间范围筛选
    if (startDate) {
      whereClause += ' AND apply_time >= ?';
      queryParams.push(startDate);
    }
    if (endDate) {
      whereClause += ' AND apply_time <= ?';
      queryParams.push(endDate);
    }

    console.log('查询条件:', { whereClause, queryParams });

    // 查询总数
    const [countResult] = await promisePool.execute(
      `SELECT COUNT(*) as total FROM min_living_allowance_applications ${whereClause}`,
      queryParams
    );
    const total = countResult[0].total;

    console.log('总数查询完成:', total);

    // 查询申请列表
    const [applications] = await promisePool.execute(
      `SELECT 
        id, application_no, user_id, status, applicant_name, applicant_id_card, 
        applicant_phone, applicant_address, family_size, monthly_income, 
        income_source, difficulty_reason, id_card_image, household_image, 
        income_proof_image, reviewer_name, review_comment, review_time, 
        processor_name, process_comment, process_time, apply_time, 
        deadline, created_at, updated_at
      FROM min_living_allowance_applications 
      ${whereClause}
      ORDER BY apply_time DESC 
      LIMIT ${limitNum} OFFSET ${offset}`,
      queryParams
    );

    console.log('申请列表查询完成，结果数量:', applications.length);

    // 计算分页信息
    const totalPages = Math.ceil(total / limitNum);
    const hasNextPage = pageNum < totalPages;
    const hasPrevPage = pageNum > 1;

    // 格式化返回数据
    const formattedApplications = applications.map(app => {
      // 确保状态和审核意见的一致性
      let reviewComment = app.review_comment;
      let processComment = app.process_comment;
      
      // 如果状态是已驳回，但审核意见是正面的，需要修正
      if (app.status === 'rejected') {
        if (reviewComment && (reviewComment.includes('通过') || reviewComment.includes('符合要求'))) {
          console.warn(`申请ID ${app.id} 状态为已驳回但审核意见为正面，数据不一致`);
          reviewComment = '申请材料不符合要求，请重新提交';
        }
      }
      
      // 如果状态是已通过，但审核意见是负面的，也需要修正
      if (app.status === 'approved') {
        if (reviewComment && (reviewComment.includes('不符合') || reviewComment.includes('驳回'))) {
          console.warn(`申请ID ${app.id} 状态为已通过但审核意见为负面，数据不一致`);
          reviewComment = '申请材料齐全，符合申请条件';
        }
      }
      
      return {
        id: app.id.toString(),
        applicationNo: app.application_no,
        userId: app.user_id.toString(),
        status: app.status,
        statusText: app.status === 'pending' ? '待审核' : 
                    app.status === 'approved' ? '已通过' : 
                    app.status === 'rejected' ? '已驳回' : 
                    app.status === 'completed' ? '已完成' : '未知状态',
        applicantName: app.applicant_name,
        applicantIdCard: app.applicant_id_card,
        applicantPhone: app.applicant_phone,
        applicantAddress: app.applicant_address,
        familySize: app.family_size,
        monthlyIncome: app.monthly_income,
        incomeSource: app.income_source,
        difficultyReason: app.difficulty_reason,
        idCardImage: app.id_card_image,
        householdImage: app.household_image,
        incomeProofImage: app.income_proof_image,
        reviewerName: app.reviewer_name,
        reviewComment: reviewComment,
        reviewTime: app.review_time ? new Date(app.review_time).toISOString().slice(0, 19).replace('T', ' ') : null,
        processorName: app.processor_name,
        processComment: processComment,
        processTime: app.process_time ? new Date(app.process_time).toISOString().slice(0, 19).replace('T', ' ') : null,
        applyTime: new Date(app.apply_time).toISOString().slice(0, 19).replace('T', ' '),
        deadline: app.deadline ? new Date(app.deadline).toISOString().slice(0, 19).replace('T', ' ') : null,
        createdAt: new Date(app.created_at).toISOString().slice(0, 19).replace('T', ' '),
        updatedAt: new Date(app.updated_at).toISOString().slice(0, 19).replace('T', ' ')
      };
    });

    res.json({
      success: true,
      message: '获取申请列表成功',
      data: {
        applications: formattedApplications,
        pagination: {
          page: pageNum,
          limit: limitNum,
          total: total,
          pages: totalPages,
          hasNextPage: hasNextPage,
          hasPrevPage: hasPrevPage
        }
      }
    });

  } catch (error) {
    console.error('获取申请列表接口错误:', error);
    console.error('错误详情:', {
      message: error.message,
      code: error.code,
      errno: error.errno,
      sqlState: error.sqlState,
      sqlMessage: error.sqlMessage,
      stack: error.stack
    });
    
    // 根据错误类型返回不同的错误信息
    let errorMessage = '服务器内部错误';
    if (error.code === 'ER_NO_SUCH_TABLE') {
      errorMessage = '数据表不存在，请检查数据库配置';
    } else if (error.code === 'ER_ACCESS_DENIED_ERROR') {
      errorMessage = '数据库访问被拒绝，请检查数据库连接配置';
    } else if (error.code === 'ECONNREFUSED') {
      errorMessage = '无法连接到数据库服务器';
    } else if (error.code === 'ER_BAD_DB_ERROR') {
      errorMessage = '数据库不存在，请先创建数据库';
    }
    
    res.status(500).json({
      success: false,
      message: errorMessage,
      error: process.env.NODE_ENV === 'development' ? {
        message: error.message,
        code: error.code,
        stack: error.stack
      } : undefined
    });
  }
});

// 获取申请进度详情
router.get('/progress/:applicationId', async (req, res) => {
  try {
    const { applicationId } = req.params;
    const { userId } = req.query;

    console.log('获取申请进度详情请求:', { applicationId, userId });

    // 验证参数
    if (!applicationId || isNaN(parseInt(applicationId))) {
      return res.status(400).json({
        success: false,
        message: '申请ID格式不正确'
      });
    }

    if (!userId || isNaN(parseInt(userId))) {
      return res.status(400).json({
        success: false,
        message: '用户ID格式不正确'
      });
    }

    // 查询申请基本信息
    const [applications] = await promisePool.execute(
      `SELECT 
        id, application_no, user_id, status, applicant_name, applicant_id_card, 
        applicant_phone, applicant_address, family_size, monthly_income, 
        income_source, difficulty_reason, id_card_image, household_image, 
        income_proof_image, reviewer_name, review_comment, review_time, 
        processor_name, process_comment, process_time, apply_time, 
        deadline, created_at, updated_at
      FROM min_living_allowance_applications 
      WHERE id = ? AND user_id = ? AND deleted_at IS NULL`,
      [applicationId, userId]
    );

    if (applications.length === 0) {
      return res.status(404).json({
        success: false,
        message: '申请记录不存在或无权限访问'
      });
    }

    const application = applications[0];

    // 查询申请进度记录
    const [progressRecords] = await promisePool.execute(
      `SELECT 
        id, step_name, step_status, step_order, operator_name, 
        operation_time, operation_comment, next_step, created_at
      FROM min_living_allowance_progress 
      WHERE application_id = ? 
      ORDER BY step_order ASC, created_at ASC`,
      [applicationId]
    );

    // 查询处理记录
    const [processLogs] = await promisePool.execute(
      `SELECT 
        id, action_type, action_description, operator_name, 
        operator_role, action_comment, action_time, created_at
      FROM min_living_allowance_process_logs 
      WHERE application_id = ? 
      ORDER BY action_time ASC, created_at ASC`,
      [applicationId]
    );

    // 查询附件信息
    const [attachments] = await promisePool.execute(
      `SELECT 
        id, attachment_type, file_name, file_url, file_size, 
        file_type, upload_time, created_at
      FROM min_living_allowance_attachments 
      WHERE application_id = ? 
      ORDER BY upload_time ASC`,
      [applicationId]
    );

    // 格式化进度步骤
    const progressSteps = progressRecords.map(record => ({
      id: record.id,
      name: record.step_name,
      status: record.step_status === 'completed' ? 'completed' : 'pending',
      description: record.operation_comment || '等待处理',
      operator: record.operator_name,
      time: record.operation_time ? new Date(record.operation_time).toISOString().slice(0, 19).replace('T', ' ') : null,
      nextStep: record.next_step
    }));

    // 格式化处理记录
    const processLog = processLogs.map(log => ({
      id: log.id,
      action: log.action_description,
      status: log.action_type === 'approve' ? 'completed' : 
              log.action_type === 'reject' ? 'rejected' : 'pending',
      operator: log.operator_name,
      role: log.operator_role,
      comment: log.action_comment,
      time: new Date(log.action_time).toISOString().slice(0, 19).replace('T', ' ')
    }));

    // 格式化附件信息
    const formattedAttachments = attachments.map(attachment => ({
      id: attachment.id,
      name: attachment.file_name,
      url: attachment.file_url,
      size: attachment.file_size ? `${(attachment.file_size / 1024).toFixed(1)}KB` : '未知',
      type: attachment.attachment_type,
      uploadTime: new Date(attachment.upload_time).toISOString().slice(0, 19).replace('T', ' ')
    }));

    // 状态文本映射
    const statusTextMap = {
      'pending': '待审核',
      'approved': '已通过',
      'rejected': '已驳回',
      'completed': '已完成'
    };

    // 构建响应数据
    const responseData = {
      success: true,
      message: '获取申请进度详情成功',
      data: {
        applicationInfo: {
          id: application.id.toString(),
          applicationNo: application.application_no,
          userId: application.user_id.toString(),
          status: application.status,
          statusText: statusTextMap[application.status] || '未知状态',
          title: '最低生活保障申请',
          category: '民政社保',
          dept: '民政办',
          description: '困难群众最低生活保障资格申请与复核',
          applicantName: application.applicant_name,
          applicantIdCard: application.applicant_id_card,
          applicantPhone: application.applicant_phone,
          applicantAddress: application.applicant_address,
          familySize: application.family_size,
          monthlyIncome: application.monthly_income,
          incomeSource: application.income_source,
          difficultyReason: application.difficulty_reason,
          idCardImage: application.id_card_image,
          householdImage: application.household_image,
          incomeProofImage: application.income_proof_image,
          reviewerName: application.reviewer_name,
          reviewComment: application.review_comment,
          reviewTime: application.review_time ? new Date(application.review_time).toISOString().slice(0, 19).replace('T', ' ') : null,
          processorName: application.processor_name,
          processComment: application.process_comment,
          processTime: application.process_time ? new Date(application.process_time).toISOString().slice(0, 19).replace('T', ' ') : null,
          applyTime: new Date(application.apply_time).toISOString().slice(0, 19).replace('T', ' '),
          deadline: application.deadline ? new Date(application.deadline).toISOString().slice(0, 19).replace('T', ' ') : null,
          createdAt: new Date(application.created_at).toISOString().slice(0, 19).replace('T', ' '),
          updatedAt: new Date(application.updated_at).toISOString().slice(0, 19).replace('T', ' '),
          attachments: formattedAttachments
        },
        progressSteps: progressSteps,
        processLog: processLog,
        contactInfo: {
          phone: '0373-1234567',
          email: 'mla@village.gov.cn',
          address: '河南省新乡县七里营村村委会'
        }
      }
    };

    console.log('申请进度详情查询完成，申请ID:', applicationId);
    res.json(responseData);

  } catch (error) {
    console.error('获取申请进度详情接口错误:', error);
    console.error('错误详情:', {
      message: error.message,
      code: error.code,
      errno: error.errno,
      sqlState: error.sqlState,
      sqlMessage: error.sqlMessage,
      stack: error.stack
    });
    
    // 根据错误类型返回不同的错误信息
    let errorMessage = '服务器内部错误';
    if (error.code === 'ER_NO_SUCH_TABLE') {
      errorMessage = '数据表不存在，请检查数据库配置';
    } else if (error.code === 'ER_ACCESS_DENIED_ERROR') {
      errorMessage = '数据库访问被拒绝，请检查数据库连接配置';
    } else if (error.code === 'ECONNREFUSED') {
      errorMessage = '无法连接到数据库服务器';
    } else if (error.code === 'ER_BAD_DB_ERROR') {
      errorMessage = '数据库不存在，请先创建数据库';
    }
    
    res.status(500).json({
      success: false,
      message: errorMessage,
      error: process.env.NODE_ENV === 'development' ? {
        message: error.message,
        code: error.code,
        stack: error.stack
      } : undefined
    });
  }
});

// 获取所有非管理员用户的申请列表
router.get('/all-applications', async (req, res) => {
  try {
    const { page = 1, limit = 10, status, keyword, startDate, endDate } = req.query;

    console.log('获取所有非管理员用户申请列表请求:', { page, limit, status, keyword, startDate, endDate });

    // 验证管理员权限
    const userId = req.headers['user-id'] || req.query.userId;
    const userRole = req.headers['user-role'] || req.query.userRole;
    
    if (!userId) {
      return res.status(401).json({
        success: false,
        message: '用户未登录'
      });
    }

    if (!userRole || userRole !== 'admin') {
      return res.status(403).json({
        success: false,
        message: '权限不足，仅管理员可访问'
      });
    }

    // 验证管理员用户是否存在
    try {
      const [users] = await promisePool.execute(
        'SELECT id, name, role FROM users WHERE id = ? AND role = "admin" AND deleted_at IS NULL',
        [userId]
      );

      if (users.length === 0) {
        return res.status(403).json({
          success: false,
          message: '管理员身份验证失败'
        });
      }

      console.log('管理员身份验证通过:', users[0]);
    } catch (userError) {
      console.error('验证管理员身份失败:', userError);
      console.error('错误详情:', {
        message: userError.message,
        code: userError.code,
        errno: userError.errno,
        sqlState: userError.sqlState,
        sqlMessage: userError.sqlMessage,
        stack: userError.stack
      });
      
      // 根据错误类型返回不同的错误信息
      let errorMessage = '服务器内部错误';
      if (userError.code === 'ER_NO_SUCH_TABLE') {
        errorMessage = '数据表不存在，请检查数据库配置';
      } else if (userError.code === 'ER_ACCESS_DENIED_ERROR') {
        errorMessage = '数据库访问被拒绝，请检查数据库连接配置';
      } else if (userError.code === 'ECONNREFUSED') {
        errorMessage = '无法连接到数据库服务器';
      } else if (userError.code === 'ER_BAD_DB_ERROR') {
        errorMessage = '数据库不存在，请先创建数据库';
      } else {
        errorMessage = '身份验证失败';
      }
      
      return res.status(500).json({
        success: false,
        message: errorMessage,
        error: process.env.NODE_ENV === 'development' ? {
          message: userError.message,
          code: userError.code,
          stack: userError.stack
        } : undefined
      });
    }

    // 验证分页参数
    const pageNum = parseInt(page);
    const limitNum = parseInt(limit);
    const offset = (pageNum - 1) * limitNum;

    if (pageNum < 1 || limitNum < 1 || limitNum > 100) {
      return res.status(400).json({
        success: false,
        message: '分页参数不正确'
      });
    }

    // 构建查询条件 - 只查询非管理员用户的申请
    let whereClause = 'WHERE mla.deleted_at IS NULL AND u.role != "admin"';
    let queryParams = [];

    // 添加状态筛选
    if (status && ['pending', 'processing', 'approved', 'rejected', 'completed'].includes(status)) {
      whereClause += ' AND mla.status = ?';
      queryParams.push(status);
    }

    // 添加关键词搜索
    if (keyword) {
      whereClause += ' AND (mla.applicant_name LIKE ? OR mla.applicant_phone LIKE ? OR mla.application_no LIKE ?)';
      const searchKeyword = `%${keyword}%`;
      queryParams.push(searchKeyword, searchKeyword, searchKeyword);
    }

    // 添加时间范围筛选
    if (startDate) {
      whereClause += ' AND mla.apply_time >= ?';
      queryParams.push(startDate);
    }
    if (endDate) {
      whereClause += ' AND mla.apply_time <= ?';
      queryParams.push(endDate);
    }

    console.log('查询条件:', { whereClause, queryParams });

    // 查询总数
    const [countResult] = await promisePool.execute(
      `SELECT COUNT(*) as total 
       FROM min_living_allowance_applications mla
       LEFT JOIN users u ON mla.user_id = u.id
       ${whereClause}`,
      queryParams
    );
    const total = countResult[0].total;

    console.log('总数查询完成:', total);

    // 查询申请列表
    const [applications] = await promisePool.execute(
      `SELECT 
        mla.id, mla.application_no, mla.user_id, mla.status, 
        mla.applicant_name, mla.applicant_id_card, mla.applicant_phone, 
        mla.applicant_address, mla.family_size, mla.monthly_income, 
        mla.income_source, mla.difficulty_reason, mla.id_card_image, 
        mla.household_image, mla.income_proof_image, mla.reviewer_name, 
        mla.review_comment, mla.review_time, mla.processor_name, 
        mla.process_comment, mla.process_time, mla.apply_time, 
        mla.deadline, mla.created_at, mla.updated_at,
        u.name as user_name, u.phone as user_phone, u.email as user_email
      FROM min_living_allowance_applications mla
      LEFT JOIN users u ON mla.user_id = u.id
      ${whereClause}
      ORDER BY mla.apply_time DESC 
      LIMIT ${limitNum} OFFSET ${offset}`,
      queryParams
    );

    console.log('申请列表查询完成，结果数量:', applications.length);

    // 计算分页信息
    const totalPages = Math.ceil(total / limitNum);
    const hasNextPage = pageNum < totalPages;
    const hasPrevPage = pageNum > 1;

    // 格式化返回数据
    const formattedApplications = applications.map(app => {
      // 使用数据验证工具确保状态和审核意见的一致性
      const validatedApp = validateApplicationConsistency({
        id: app.id,
        status: app.status,
        review_comment: app.review_comment,
        process_comment: app.process_comment
      });
      
      return {
        id: app.id.toString(),
        applicationNo: app.application_no,
        userId: app.user_id.toString(),
        status: app.status,
        statusText: app.status === 'pending' ? '待审核' : 
                    app.status === 'approved' ? '已通过' : 
                    app.status === 'rejected' ? '已驳回' : 
                    app.status === 'completed' ? '已完成' : '未知状态',
        applicantName: app.applicant_name,
        applicantIdCard: app.applicant_id_card,
        applicantPhone: app.applicant_phone,
        applicantAddress: app.applicant_address,
        familySize: app.family_size,
        monthlyIncome: app.monthly_income,
        incomeSource: app.income_source,
        difficultyReason: app.difficulty_reason,
        idCardImage: app.id_card_image,
        householdImage: app.household_image,
        incomeProofImage: app.income_proof_image,
        reviewerName: app.reviewer_name,
        reviewComment: validatedApp.reviewComment,
        reviewTime: app.review_time ? new Date(app.review_time).toISOString().slice(0, 19).replace('T', ' ') : null,
        processorName: app.processor_name,
        processComment: validatedApp.processComment,
        processTime: app.process_time ? new Date(app.process_time).toISOString().slice(0, 19).replace('T', ' ') : null,
        applyTime: new Date(app.apply_time).toISOString().slice(0, 19).replace('T', ' '),
        deadline: app.deadline ? new Date(app.deadline).toISOString().slice(0, 19).replace('T', ' ') : null,
        createdAt: new Date(app.created_at).toISOString().slice(0, 19).replace('T', ' '),
        updatedAt: new Date(app.updated_at).toISOString().slice(0, 19).replace('T', ' '),
        // 用户信息
        userName: app.user_name,
        userPhone: app.user_phone,
        userEmail: app.user_email
      };
    });

    res.json({
      success: true,
      message: '获取所有非管理员用户申请列表成功',
      data: {
        applications: formattedApplications,
        pagination: {
          page: pageNum,
          limit: limitNum,
          total: total,
          pages: totalPages,
          hasNextPage: hasNextPage,
          hasPrevPage: hasPrevPage
        }
      }
    });

  } catch (error) {
    console.error('获取所有非管理员用户申请列表接口错误:', error);
    console.error('错误详情:', {
      message: error.message,
      code: error.code,
      errno: error.errno,
      sqlState: error.sqlState,
      sqlMessage: error.sqlMessage,
      stack: error.stack
    });
    
    // 根据错误类型返回不同的错误信息
    let errorMessage = '服务器内部错误';
    if (error.code === 'ER_NO_SUCH_TABLE') {
      errorMessage = '数据表不存在，请检查数据库配置';
    } else if (error.code === 'ER_ACCESS_DENIED_ERROR') {
      errorMessage = '数据库访问被拒绝，请检查数据库连接配置';
    } else if (error.code === 'ECONNREFUSED') {
      errorMessage = '无法连接到数据库服务器';
    } else if (error.code === 'ER_BAD_DB_ERROR') {
      errorMessage = '数据库不存在，请先创建数据库';
    }
    
    res.status(500).json({
      success: false,
      message: errorMessage,
      error: process.env.NODE_ENV === 'development' ? {
        message: error.message,
        code: error.code,
        stack: error.stack
      } : undefined
    });
  }
});

// 获取申请详情
router.get('/detail/:applicationId', async (req, res) => {
  try {
    const { applicationId } = req.params;
    const { userId, userRole } = req.query;

    console.log('获取申请详情请求:', { applicationId, userId, userRole });

    // 验证参数
    if (!applicationId || isNaN(parseInt(applicationId))) {
      return res.status(400).json({
        success: false,
        message: '申请ID格式不正确'
      });
    }

    // 验证用户权限
    if (!userId || isNaN(parseInt(userId))) {
      return res.status(400).json({
        success: false,
        message: '用户ID格式不正确'
      });
    }

    // 查询申请基本信息
    const [applications] = await promisePool.execute(
      `SELECT 
        id, application_no, user_id, status, applicant_name, applicant_id_card, 
        applicant_phone, applicant_address, family_size, monthly_income, 
        income_source, difficulty_reason, id_card_image, household_image, 
        income_proof_image, reviewer_id, reviewer_name, review_comment, 
        review_time, processor_id, processor_name, process_comment, 
        process_time, apply_time, deadline, created_at, updated_at
      FROM min_living_allowance_applications 
      WHERE id = ? AND deleted_at IS NULL`,
      [applicationId]
    );

    if (applications.length === 0) {
      return res.status(404).json({
        success: false,
        message: '申请记录不存在'
      });
    }

    const application = applications[0];

    // 权限检查：只有申请人本人或管理员可以查看详情
    const isOwner = application.user_id.toString() === userId.toString();
    const isAdmin = userRole === 'admin';

    if (!isOwner && !isAdmin) {
      return res.status(403).json({
        success: false,
        message: '权限不足，无法查看该申请详情'
      });
    }

    // 查询申请进度记录
    const [progressRecords] = await promisePool.execute(
      `SELECT 
        id, step_name, step_status, step_order, operator_name, 
        operation_time, operation_comment, next_step, created_at
      FROM min_living_allowance_progress 
      WHERE application_id = ? 
      ORDER BY step_order ASC, created_at ASC`,
      [applicationId]
    );

    // 查询处理记录
    const [processLogs] = await promisePool.execute(
      `SELECT 
        id, action_type, action_description, operator_name, 
        operator_role, action_comment, action_time, created_at
      FROM min_living_allowance_process_logs 
      WHERE application_id = ? 
      ORDER BY action_time ASC, created_at ASC`,
      [applicationId]
    );

    // 查询附件信息
    const [attachments] = await promisePool.execute(
      `SELECT 
        id, attachment_type, file_name, file_url, file_size, 
        file_type, upload_time, created_at
      FROM min_living_allowance_attachments 
      WHERE application_id = ? 
      ORDER BY upload_time ASC`,
      [applicationId]
    );

    // 格式化进度步骤
    const progressSteps = progressRecords.map(record => ({
      id: record.id,
      name: record.step_name,
      status: record.step_status === 'completed' ? 'completed' : 'pending',
      description: record.operation_comment || '等待处理',
      operator: record.operator_name,
      time: record.operation_time ? new Date(record.operation_time).toISOString().slice(0, 19).replace('T', ' ') : null,
      nextStep: record.next_step
    }));

    // 格式化处理记录
    const processLog = processLogs.map(log => ({
      id: log.id,
      action: log.action_description,
      status: log.action_type === 'approve' ? 'completed' : 
              log.action_type === 'reject' ? 'rejected' : 'pending',
      operator: log.operator_name,
      role: log.operator_role,
      comment: log.action_comment,
      time: new Date(log.action_time).toISOString().slice(0, 19).replace('T', ' ')
    }));

    // 格式化附件信息
    const formattedAttachments = attachments.map(attachment => ({
      id: attachment.id,
      name: attachment.file_name,
      url: attachment.file_url,
      size: attachment.file_size ? `${(attachment.file_size / 1024).toFixed(1)}KB` : '未知',
      type: attachment.attachment_type,
      uploadTime: new Date(attachment.upload_time).toISOString().slice(0, 19).replace('T', ' ')
    }));

    // 状态文本映射
    const statusTextMap = {
      'pending': '待审核',
      'approved': '已通过',
      'rejected': '已驳回',
      'completed': '已完成'
    };

    // 使用数据验证工具确保状态和审核意见的一致性
    const validatedApp = validateApplicationConsistency({
      id: application.id,
      status: application.status,
      review_comment: application.review_comment,
      process_comment: application.process_comment
    });

    // 构建响应数据
    const responseData = {
      success: true,
      message: '获取申请详情成功',
      data: {
        applicationInfo: {
          id: application.id.toString(),
          applicationNo: application.application_no,
          userId: application.user_id.toString(),
          status: application.status,
          statusText: statusTextMap[application.status] || '未知状态',
          title: '最低生活保障申请',
          category: '民政社保',
          dept: '民政办',
          description: '困难群众最低生活保障资格申请与复核',
          applicantName: application.applicant_name,
          applicantIdCard: application.applicant_id_card,
          applicantPhone: application.applicant_phone,
          applicantAddress: application.applicant_address,
          familySize: application.family_size,
          monthlyIncome: application.monthly_income,
          incomeSource: application.income_source,
          difficultyReason: application.difficulty_reason,
          idCardImage: application.id_card_image,
          householdImage: application.household_image,
          incomeProofImage: application.income_proof_image,
          reviewerId: application.reviewer_id ? application.reviewer_id.toString() : null,
          reviewerName: application.reviewer_name,
          reviewComment: validatedApp.reviewComment,
          reviewTime: application.review_time ? new Date(application.review_time).toISOString().slice(0, 19).replace('T', ' ') : null,
          processorId: application.processor_id ? application.processor_id.toString() : null,
          processorName: application.processor_name,
          processComment: validatedApp.processComment,
          processTime: application.process_time ? new Date(application.process_time).toISOString().slice(0, 19).replace('T', ' ') : null,
          applyTime: new Date(application.apply_time).toISOString().slice(0, 19).replace('T', ' '),
          deadline: application.deadline ? new Date(application.deadline).toISOString().slice(0, 19).replace('T', ' ') : null,
          createdAt: new Date(application.created_at).toISOString().slice(0, 19).replace('T', ' '),
          updatedAt: new Date(application.updated_at).toISOString().slice(0, 19).replace('T', ' '),
          attachments: formattedAttachments
        },
        progressSteps: progressSteps,
        processLog: processLog,
        contactInfo: {
          phone: '0373-1234567',
          email: 'mla@village.gov.cn',
          address: '河南省新乡县七里营村村委会'
        }
      }
    };

    console.log('申请详情查询完成，申请ID:', applicationId);
    res.json(responseData);

  } catch (error) {
    console.error('获取申请详情接口错误:', error);
    console.error('错误详情:', {
      message: error.message,
      code: error.code,
      errno: error.errno,
      sqlState: error.sqlState,
      sqlMessage: error.sqlMessage,
      stack: error.stack
    });
    
    // 根据错误类型返回不同的错误信息
    let errorMessage = '服务器内部错误';
    if (error.code === 'ER_NO_SUCH_TABLE') {
      errorMessage = '数据表不存在，请检查数据库配置';
    } else if (error.code === 'ER_ACCESS_DENIED_ERROR') {
      errorMessage = '数据库访问被拒绝，请检查数据库连接配置';
    } else if (error.code === 'ECONNREFUSED') {
      errorMessage = '无法连接到数据库服务器';
    } else if (error.code === 'ER_BAD_DB_ERROR') {
      errorMessage = '数据库不存在，请先创建数据库';
    }
    
    res.status(500).json({
      success: false,
      message: errorMessage,
      error: process.env.NODE_ENV === 'development' ? {
        message: error.message,
        code: error.code,
        stack: error.stack
      } : undefined
    });
  }
});

// 审核申请（通过/驳回）
router.post('/review', async (req, res) => {
  try {
    // 验证请求数据
    const { error, value } = mlaReviewSchema.validate(req.body);
    if (error) {
      return res.status(400).json({
        success: false,
        message: error.details[0].message
      });
    }

    const {
      applicationId,
      action,
      comment,
      operatorId,
      operatorName
    } = value;

    console.log('审核申请请求:', { applicationId, action, comment, operatorId, operatorName });

    // 检查申请是否存在
    const [applications] = await promisePool.execute(
      'SELECT id, status, user_id, applicant_name FROM min_living_allowance_applications WHERE id = ? AND deleted_at IS NULL',
      [applicationId]
    );

    if (applications.length === 0) {
      return res.status(404).json({
        success: false,
        message: '申请记录不存在'
      });
    }

    const application = applications[0];

    // 检查申请状态是否允许审核
    // 允许对 pending、approved、rejected、completed 状态的申请进行重新处理
    const allowedStatuses = ['pending', 'approved', 'rejected', 'completed'];
    if (!allowedStatuses.includes(application.status)) {
      return res.status(400).json({
        success: false,
        message: '该申请状态不允许审核操作'
      });
    }

    // 检查操作人权限（管理员）
    const [operators] = await promisePool.execute(
      'SELECT id, name, role FROM users WHERE id = ? AND role = "admin" AND deleted_at IS NULL',
      [operatorId]
    );

    if (operators.length === 0) {
      return res.status(403).json({
        success: false,
        message: '操作人权限不足'
      });
    }

    // 确定新的状态
    // 如果是重新处理，根据操作类型确定新状态
    let newStatus;
    let actionDescription;
    let actionType;
    
    if (action === 'approve') {
      newStatus = 'approved';
      actionDescription = application.status === 'approved' ? '重新审核通过' : '审核通过';
      actionType = 'approve';
    } else {
      newStatus = 'rejected';
      actionDescription = application.status === 'rejected' ? '重新审核驳回' : '审核驳回';
      actionType = 'reject';
    }

    // 开始事务
    const connection = await promisePool.getConnection();
    await connection.beginTransaction();

    try {
      // 更新申请状态
      const updateFields = action === 'approve' 
        ? 'status = ?, reviewer_id = ?, reviewer_name = ?, review_comment = ?, review_time = NOW()'
        : 'status = ?, reviewer_id = ?, reviewer_name = ?, review_comment = ?, review_time = NOW()';
      
      await connection.execute(
        `UPDATE min_living_allowance_applications 
         SET ${updateFields}, updated_at = NOW() 
         WHERE id = ?`,
        [
          newStatus, operatorId, operatorName, comment, applicationId
        ]
      );

      // 如果是重新处理，先删除当前操作人之前的处理记录，然后插入新的
      if (application.status === 'approved' || application.status === 'rejected') {
        // 删除当前操作人之前的审核记录（只删除当前操作人的记录）
        await connection.execute(
          `DELETE FROM min_living_allowance_progress 
           WHERE application_id = ? AND step_order >= 3 AND operator_id = ?`,
          [applicationId, operatorId]
        );
        
        // 删除当前操作人之前的处理记录（只删除当前操作人的记录）
        await connection.execute(
          `DELETE FROM min_living_allowance_process_logs 
           WHERE application_id = ? AND action_type IN ('approve', 'reject') AND operator_id = ?`,
          [applicationId, operatorId]
        );
      }
      
      // 更新进度记录
      const stepName = action === 'approve' ? 
        (application.status === 'approved' ? '重新审核通过' : '审核通过') :
        (application.status === 'rejected' ? '重新审核驳回' : '审核驳回');
      
      const nextStep = action === 'approve' ? '等待处理' : '申请结束';
      
      await connection.execute(
        `INSERT INTO min_living_allowance_progress (
          application_id, step_name, step_status, step_order, 
          operator_id, operator_name, operation_time, operation_comment, 
          next_step, created_at, updated_at
        ) VALUES (?, ?, ?, ?, ?, ?, NOW(), ?, ?, NOW(), NOW())`,
        [
          applicationId, 
          stepName, 
          'completed', 
          3, 
          operatorId, 
          operatorName, 
          comment,
          nextStep
        ]
      );

      // 添加处理记录
      await connection.execute(
        `INSERT INTO min_living_allowance_process_logs (
          application_id, action_type, action_description, operator_id, 
          operator_name, operator_role, action_comment, action_time, 
          created_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), NOW())`,
        [
          applicationId, actionType, actionDescription, operatorId,
          operatorName, 'admin', comment
        ]
      );

      // 如果是通过，添加处理人信息
      if (action === 'approve') {
        await connection.execute(
          `UPDATE min_living_allowance_applications 
           SET processor_id = ?, processor_name = ?, process_comment = ?, process_time = NOW(), updated_at = NOW()
           WHERE id = ?`,
          [operatorId, operatorName, comment, applicationId]
        );
      }

      // 提交事务
      await connection.commit();

      res.json({
        success: true,
        message: action === 'approve' ? '申请审核通过' : '申请已驳回',
        data: {
          applicationId: applicationId,
          status: newStatus,
          action: action,
          operatorName: operatorName,
          reviewTime: new Date().toISOString()
        }
      });

    } catch (error) {
      // 回滚事务
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }

  } catch (error) {
    console.error('审核申请接口错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});

// 获取用户非已完成状态的申请总数量
router.get('/count/:userId', async (req, res) => {
  try {
    const { userId } = req.params;

    console.log('获取用户申请数量请求:', { userId });

    // 验证用户ID
    if (!userId || isNaN(parseInt(userId))) {
      return res.status(400).json({
        success: false,
        message: '用户ID格式不正确'
      });
    }

    // 查询用户所有申请总数
    const [countResult] = await promisePool.execute(
      `SELECT COUNT(*) as total 
       FROM min_living_allowance_applications 
       WHERE user_id = ? 
       AND deleted_at IS NULL`,
      [userId]
    );

    const totalCount = countResult[0].total;

    console.log('用户申请数量查询完成:', { userId, totalCount });

    res.json({
      success: true,
      message: '获取申请数量成功',
      data: {
        userId: userId,
        totalCount: totalCount,
        statusText: '所有申请总数'
      }
    });

  } catch (error) {
    console.error('获取申请数量接口错误:', error);
    console.error('错误详情:', {
      message: error.message,
      code: error.code,
      errno: error.errno,
      sqlState: error.sqlState,
      sqlMessage: error.sqlMessage,
      stack: error.stack
    });
    
    // 根据错误类型返回不同的错误信息
    let errorMessage = '服务器内部错误';
    if (error.code === 'ER_NO_SUCH_TABLE') {
      errorMessage = '数据表不存在，请检查数据库配置';
    } else if (error.code === 'ER_ACCESS_DENIED_ERROR') {
      errorMessage = '数据库访问被拒绝，请检查数据库连接配置';
    } else if (error.code === 'ECONNREFUSED') {
      errorMessage = '无法连接到数据库服务器';
    } else if (error.code === 'ER_BAD_DB_ERROR') {
      errorMessage = '数据库不存在，请先创建数据库';
    }
    
    res.status(500).json({
      success: false,
      message: errorMessage,
      error: process.env.NODE_ENV === 'development' ? {
        message: error.message,
        code: error.code,
        stack: error.stack
      } : undefined
    });
  }
});

// 获取管理员总处理次数
router.get('/admin/process-count/:adminId', async (req, res) => {
  try {
    const { adminId } = req.params;

    console.log('获取管理员总处理次数请求:', { adminId });

    // 验证管理员ID
    if (!adminId || isNaN(parseInt(adminId))) {
      return res.status(400).json({
        success: false,
        message: '管理员ID格式不正确'
      });
    }

    // 验证管理员身份
    const [admins] = await promisePool.execute(
      'SELECT id, name, role FROM users WHERE id = ? AND role = "admin" AND deleted_at IS NULL',
      [adminId]
    );

    if (admins.length === 0) {
      return res.status(404).json({
        success: false,
        message: '管理员不存在'
      });
    }

    // 统计管理员对村民申请的总处理次数
    // 使用DISTINCT确保同一个申请多次处理不累加
    const [processCountResult] = await promisePool.execute(
      `SELECT COUNT(DISTINCT mla.application_id) as process_count
       FROM min_living_allowance_process_logs mla
       INNER JOIN min_living_allowance_applications app ON mla.application_id = app.id
       INNER JOIN users u ON app.user_id = u.id
       WHERE mla.operator_id = ? 
       AND mla.operator_role = 'admin'
       AND mla.action_type IN ('approve', 'reject', 'review')
       AND u.role != 'admin'
       AND app.deleted_at IS NULL
       AND u.deleted_at IS NULL`,
      [adminId]
    );

    // 统计管理员今日对村民申请的处理次数
    const [todayProcessCountResult] = await promisePool.execute(
      `SELECT COUNT(DISTINCT mla.application_id) as today_process_count
       FROM min_living_allowance_process_logs mla
       INNER JOIN min_living_allowance_applications app ON mla.application_id = app.id
       INNER JOIN users u ON app.user_id = u.id
       WHERE mla.operator_id = ? 
       AND mla.operator_role = 'admin'
       AND mla.action_type IN ('approve', 'reject', 'review')
       AND u.role != 'admin'
       AND app.deleted_at IS NULL
       AND u.deleted_at IS NULL
       AND DATE(mla.action_time) = CURDATE()`,
      [adminId]
    );

    const processCount = processCountResult[0].process_count;
    const todayProcessCount = todayProcessCountResult[0].today_process_count;

    console.log('管理员处理次数查询完成:', { adminId, processCount, todayProcessCount });

    res.json({
      success: true,
      message: '获取管理员处理次数成功',
      data: {
        adminId: adminId,
        adminName: admins[0].name,
        processCount: processCount,
        todayProcessCount: todayProcessCount,
        description: '对村民用户申请的处理次数（同一申请多次处理不累加）',
        todayDescription: '今日对村民用户申请的处理次数'
      }
    });

  } catch (error) {
    console.error('获取管理员处理次数接口错误:', error);
    console.error('错误详情:', {
      message: error.message,
      code: error.code,
      errno: error.errno,
      sqlState: error.sqlState,
      sqlMessage: error.sqlMessage,
      stack: error.stack
    });
    
    // 根据错误类型返回不同的错误信息
    let errorMessage = '服务器内部错误';
    if (error.code === 'ER_NO_SUCH_TABLE') {
      errorMessage = '数据表不存在，请检查数据库配置';
    } else if (error.code === 'ER_ACCESS_DENIED_ERROR') {
      errorMessage = '数据库访问被拒绝，请检查数据库连接配置';
    } else if (error.code === 'ECONNREFUSED') {
      errorMessage = '无法连接到数据库服务器';
    } else if (error.code === 'ER_BAD_DB_ERROR') {
      errorMessage = '数据库不存在，请先创建数据库';
    }
    
    res.status(500).json({
      success: false,
      message: errorMessage,
      error: process.env.NODE_ENV === 'development' ? {
        message: error.message,
        code: error.code,
        stack: error.stack
      } : undefined
    });
  }
});

module.exports = router;
